﻿Imports System.Data.SqlServerCe
Imports RSToolKit.ReportService

Public Class ReindexServer

    Private _DatabasePath As String = System.IO.Path.Combine(Application.StartupPath, "RSToolKitDB.sdf")
    Private _ConnectionString As String = "Data Source=" + _DatabasePath + ";Persist Security Info=False;"
    Private _Server As New ArrayList
    Private Connection As SqlCeConnection

    Public ReadOnly Property Count() As Integer
        Get
            Return _Server.Count
        End Get
    End Property

    Public WriteOnly Property Server() As ArrayList
        Set(ByVal value As ArrayList)
            _Server = value
        End Set
    End Property

    Public Sub New(ByVal Server As ReportServer)
        _Server.Add(Server)
    End Sub

    Public Sub Start()
        Connection = New SqlCeConnection(_ConnectionString)
        For Each ReportServer As ReportServer In _Server
            Dim ServerID As Integer = SetServer(ReportServer.URL)
            ReadItems(ServerID, "/", ReportServer)
        Next
    End Sub

    Private Sub ReadItems(ByVal ServerID As Integer, ByVal Path As String, ByVal ReportServer As ReportServer)
        Dim Items() As CatalogItem = ReportServer.ws.ListChildren(Path, False)
        For Each Item As CatalogItem In Items
            Select Case Item.Type
                Case ItemTypeEnum.Folder
                    ReadItems(ServerID, Item.Path, ReportServer)
                Case ItemTypeEnum.Report
                    Dim RI As New ReportInfo(ReportServer, Item)
                    RI.GetFieldsDataTable()
                    SetReport(RI, ServerID)
            End Select
        Next
    End Sub

    Private Sub SetFields(ByVal ReportID As Integer, ByVal Fields As DataTable)
        For Each Row As DataRow In Fields.Rows
            Dim CommandText As String = "INSERT INTO Fields([ReportID], [DataSet], [DataSource], [Schema], [ColumnOwner], [ColumnName], [TypeName]) Values(@ReportID, @DataSet, @DataSource, @Schema, @ColumnOwner, @ColumnName, @TypeName)"
            Dim Command As New SqlCeCommand(CommandText, Connection)
            Command.Parameters.Add(New SqlCeParameter("@ReportID", ReportID))
            Command.Parameters.Add(New SqlCeParameter("@DataSet", Row("DataSet")))
            Command.Parameters.Add(New SqlCeParameter("@DataSource", Row("DataSource")))
            Command.Parameters.Add(New SqlCeParameter("@Schema", Row("Schema")))
            Command.Parameters.Add(New SqlCeParameter("@ColumnOwner", Row("ColumnOwner")))
            Command.Parameters.Add(New SqlCeParameter("@ColumnName", Row("ColumnName")))
            Command.Parameters.Add(New SqlCeParameter("@TypeName", Row("TypeName")))
            Connection.Open()
            Command.ExecuteNonQuery()
            Connection.Close()
        Next
    End Sub

    Private Function SetReport(ByVal Report As ReportInfo, ByVal ServerID As Integer)
        Dim ReportID As Integer
        Try
            Dim CommandText As String = "SELECT ReportID FROM Reports WHERE ReportGUID = @ReportGUID"
            Dim Command As New SqlCeCommand(CommandText, Connection)
            Dim paramGUID As New SqlCeParameter("@ReportGUID", Report.ReportID.ToString())
            Command.Parameters.Add(paramGUID)
            Connection.Open()
            ReportID = Command.ExecuteScalar()
            Connection.Close()
            If ReportID = 0 Then
                Command.CommandText = "INSERT INTO Reports(ServerID, ReportGUID, Path, CreatedBy, CreationDate, ModifiedBy, ModifiedDate, ReportName) Values(@ServerID, @ReportGUID, @Path, @CreatedBy, @CreationDate, @ModifiedBy, @ModifiedDate, @ReportName)"
                Command.Parameters.Add(New SqlCeParameter("@ServerID", ServerID))
                Command.Parameters.Add(New SqlCeParameter("@Path", Report.Path.ToString()))
                Command.Parameters.Add(New SqlCeParameter("@CreatedBy", Report.CreationBy.ToString()))
                Command.Parameters.Add(New SqlCeParameter("@CreationDate", Report.CreationDate.ToString()))
                Command.Parameters.Add(New SqlCeParameter("@ModifiedBy", Report.ModifiedBy.ToString()))
                Command.Parameters.Add(New SqlCeParameter("@ModifiedDate", Report.ModifiedDate.ToString()))
                Command.Parameters.Add(New SqlCeParameter("@ReportName", Report.Name.ToString))
                Connection.Open()
                Command.ExecuteNonQuery()
                Command.CommandText = "SELECT @@Identity"
                ReportID = Command.ExecuteScalar
                Connection.Close()
            Else
                Dim ModifiedDate As Date = Nothing
                Command.CommandText = "SELECT ModifiedDate FROM Reports WHERE ReportGUID = @ReportGUID"
                Connection.Open()
                ModifiedDate = Command.ExecuteScalar()
                Connection.Close()
                If Date.Compare(Report.ModifiedDate.Date, ModifiedDate.Date) > 0 Then
                    Command.CommandText = "Update Reports SET ServerID = @ServerID, Path = @Path, CreatedBy = @CreatedBy, CreationDate = @CreationDate, ModifiedBy = @ModifiedBy, ModifiedDate = @ModifiedDate, ReportName = @ReportName WHERE ReportGUID = @ReportGUID"
                    Command.Parameters.Add(New SqlCeParameter("@ServerID", ServerID))
                    Command.Parameters.Add(New SqlCeParameter("@Path", Report.Path.ToString()))
                    Command.Parameters.Add(New SqlCeParameter("@CreatedBy", Report.CreationBy.ToString()))
                    Command.Parameters.Add(New SqlCeParameter("@CreationDate", Report.CreationDate.ToString()))
                    Command.Parameters.Add(New SqlCeParameter("@ModifiedBy", Report.ModifiedBy.ToString()))
                    Command.Parameters.Add(New SqlCeParameter("@ModifiedDate", Report.ModifiedDate.ToString()))
                    Command.Parameters.Add(New SqlCeParameter("@ReportName", Report.Name.ToString))
                    Connection.Open()
                    Command.ExecuteNonQuery()
                    Command.CommandText = "DELETE FROM Fields WHERE ReportGUID = @ReportGUID"
                    Command.ExecuteNonQuery()
                    Connection.Close()
                End If
            End If
            SetFields(ReportID, Report.GetFieldsDataTable)
        Catch ex As SqlCeException
            ShowException("Connection Error", ex)
        Catch ex As Exception
            ShowException("Connection Error", ex)
        End Try
        Return ReportID
    End Function

    Private Function SetServer(ByVal URL As String) As Integer
        Dim ServerID As Integer = Nothing
        Try
            Dim CommandText As String = "SELECT ServerID FROM ReportServer WHERE URL = @URL"
            Dim Command As New SqlCeCommand(CommandText, Connection)
            Command.Parameters.Add(New SqlCeParameter("@URL", URL))
            Connection.Open()
            ServerID = Command.ExecuteScalar()
            Connection.Close()
            If ServerID = 0 Then
                Command.CommandText = "INSERT INTO ReportServer(URL) Values(@URL)"
                Connection.Open()
                Command.ExecuteNonQuery()
                Command.CommandText = "SELECT @@Identity"
                ServerID = Command.ExecuteScalar
                Connection.Close()
            End If
        Catch ex As SqlCeException
            ShowException("Connection Error", ex)
        Catch ex As Exception
            ShowException("Connection Error", ex)
        End Try
        Return ServerID
    End Function

    Private Sub ShowException(ByVal Text As String, Optional ByVal ExceptionText As Exception = Nothing)
        Dim sqlMsgBox As New Microsoft.SqlServer.MessageBox.ExceptionMessageBox()
        sqlMsgBox.Text = Text
        sqlMsgBox.Caption = "RSToolKit Beta"
        sqlMsgBox.ShowToolBar = True
        sqlMsgBox.Buttons = Microsoft.SqlServer.MessageBox.ExceptionMessageBoxButtons.OK
        sqlMsgBox.Symbol = Microsoft.SqlServer.MessageBox.ExceptionMessageBoxSymbol.Error
        sqlMsgBox.HelpLink = "http://rstoolkit.codeplex.com/documentation"
        If Not IsNothing(ExceptionText) Then
            sqlMsgBox.InnerException = ExceptionText
        End If
        sqlMsgBox.Beep = True
        sqlMsgBox.Show(Me)
    End Sub

End Class
